Create  PROC sp_cancel_tape_reel_request_details
(
@tape_reel_req_id INT,
@tr_lot_id INT,				--Lot Id at Class Store.
@qty_pulled INT,
@tr_vendor_name varchar(50),
@tr_processStep_id int,
@lot_number varchar(50)
)
AS
BEGIN
	declare @fg_lot_id int
	declare @fg_processStep_id int
	declare @fg_location_id int
	
	
	select  @fg_processStep_id = PROCESSSTEP_ID from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@tr_processStep_id, 1 ) WHERE PS_TYPE = 'FG'
	select  @fg_location_id = location_id from location where L_vendor_Name = @tr_vendor_name and L_TYpe = 'Finished Goods'
	
	select @fg_lot_id = lot_id from lot_table where processStep_id = @fg_processStep_id and location_id = @fg_location_id and lot_number = @lot_number

	IF @fg_lot_id <> -1 AND @tape_reel_req_id <> -1 and @tr_lot_id <> -1
	BEGIN	
		UPDATE Lot_Table SET QTY_IN = QTY_IN + @qty_pulled WHERE LOT_ID = @fg_lot_id	
		UPDATE INVENTORY_LOT SET IL_QTY_IN = IL_QTY_IN + @qty_pulled WHERE LOT_ID = @fg_lot_id	

		UPDATE Lot_Table SET QTY_IN = QTY_IN - @qty_pulled WHERE LOT_ID = @tr_lot_id	
		UPDATE INVENTORY_LOT SET IL_QTY_IN = IL_QTY_IN - @qty_pulled WHERE LOT_ID = @tr_lot_id
			
	END
	

END --End of proc